package org.anyline.simple.all;

import com.alibaba.druid.pool.DruidDataSource;
import org.anyline.adapter.KeyAdapter;
import org.anyline.data.datasource.DataSourceHolder;
import org.anyline.data.param.ConfigStore;
import org.anyline.data.param.init.DefaultConfigStore;
import org.anyline.data.run.Run;
import org.anyline.data.runtime.RuntimeHolder;
import org.anyline.entity.DataRow;
import org.anyline.entity.DataSet;
import org.anyline.metadata.*;
import org.anyline.metadata.type.DatabaseType;
import org.anyline.net.HttpResponse;
import org.anyline.proxy.ServiceProxy;
import org.anyline.service.AnylineService;
import org.anyline.util.BasicUtil;
import org.anyline.util.ConfigTable;
import org.anyline.util.FileUtil;
import org.checkerframework.checker.units.qual.C;
import org.elasticsearch.client.Request;
import org.elasticsearch.client.Response;
import org.elasticsearch.client.RestClient;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.sql.SQLException;
import java.util.*;

@SpringBootTest(classes = BootApplication.class)
public class AllTest {
    @Autowired
    private AnylineService service          ;
    private static Logger log = LoggerFactory.getLogger(AllTest.class);

    @Test
    public void index() throws Exception{
        Table tab = service.metadata().table("tab_index");
        if(null != tab){
            service.ddl().drop(tab);
        }
        tab = new Table("tab_index");
        tab.setComment("索引测试");
        tab.addColumn("id", "int");
        tab.addColumn("code", "varchar(10)");
        tab.addColumn("name", "varchar(10)");
        Index idx_origin = new Index("idx_origin");
        idx_origin.addColumn("code");
        tab.add(idx_origin);
        service.ddl().create(tab);

        tab = service.metadata().table("tab_index");
        //改个名
        tab.getIndex("idx_origin").setNewName("idx_rename");
        //新加一个
        Index idx_new = new Index("idx_new");
        idx_new.addColumn("name");
        tab.add(idx_new);
        tab.setComment("表备注测试");
        tab.getColumn("code").setType("varchar(100)");
        tab.getColumn("name").setType("varchar(100)");
        service.ddl().save(tab);
        List<Run> runs = tab.runs();
        for(Run run:runs){
            System.out.println(run.getFinalUpdate()+";");
        }
    }

    @Test
    public void findInSet() {
        DataSet set = new DataSet();
        for(int i=0; i<10; i++){
            DataRow row = set.add();
            row.put("ID", i);
            row.put("KEY", "A"+i+",A"+i*2);
        }
        List<String> keys = new ArrayList<>();
        keys.add("A1");
        keys.add("A2");
        keys.add("A3");
        DataRow row = set.add();
        row.put("ID", "11");
        row.put("KEY", keys);

        row = set.add();
        row.put("ID", "12");
        row.put("KEY", new String[]{"A1","A2"});

        Map<String, String> maps = new HashMap<>();
        row = set.add();
        row.put("ID", "13");
        maps.put("A1", "1");
        maps.put("A2", "2");
        row.put("KEY", maps);
        //A1,A2
        //A2,A4
        //A3,A6
        //有一个存在即可 findInSetOr = findInSet
        //[{"ID":1,"KEY":"A1,A2"},{"ID":2,"KEY":"A2,A4"},{"ID":"11","KEY":["A1","A2","A3"]},{"ID":"12","KEY":["A1","A2"]},{"ID":"13","KEY":{"A1":"1","A2":"2"}}]
        DataSet filter = set.select.findInSetOr("KEY", "A1", "A2");
        System.out.println(filter);

        //A1 A2都存在才返回
        //[{"ID":1,"KEY":"A1,A2"},{"ID":"11","KEY":["A1","A2","A3"]},{"ID":"12","KEY":["A1","A2"]},{"ID":"13","KEY":{"A1":"1","A2":"2"}}]
        filter = set.select.findInSetAnd("KEY", "A1", "A2");
        System.out.println(filter);

        //忽略大小写
        //[{"ID":1,"KEY":"A1,A2"},{"ID":"11","KEY":["A1","A2","A3"]},{"ID":"12","KEY":["A1","A2"]},{"ID":"13","KEY":{"A1":"1","A2":"2"}}]
        set.select.setIgnoreCase(true);
        filter = set.select.findInSetAnd("KEY", "a1", "a2");
        System.out.println(filter);

    }

    @Test
    public void databases() throws SQLException {
        LinkedHashMap<String, Database> databases = service.metadata().databases();
        for(Database database:databases.values()){
            log.warn("数据库:{}", database.getName());
        }
    }
    @Test
    public void sort() throws Exception{
        Table table = service.metadata().table("crm_user");
        // table.getColumn("CODE").drop();
        table.sort();
        service.ddl().alter(table);
    }
    @Test
    public void truncate() throws Exception{
        ConfigTable.IS_SQL_DELIMITER_OPEN =true;
        service.truncate("crm_user");
    }
    @Test
    public void virtual() throws Exception{
        AnylineService service = ServiceProxy.service(DatabaseType.MySQL);
        ConfigStore configs = new DefaultConfigStore();
        service.querys("crm_user", configs);
        List<Run> runs = configs.runs();
        for(Run run:runs){
            System.out.println(run.getFinalQuery());
        }
        Table table = new Table("crm_user");
        table.addColumn("ID", "INT");
        service.ddl().create(table);
        List<org.anyline.data.Run> rs = table.runs();
        for (org.anyline.data.Run r:rs){
            System.out.println(r.getFinalUpdate());
        }
    }
    /**
     * 创建表
     * @throws Exception
     */
    @Test
    public void createTable() throws Exception{
        //有些数据库支持IF EXISTS可以直接drop
        //有些不支持需要先检测表是否存在
        Table table = new Table("FI_ORDER");
        if(service.metadata().exists(table)){
            service.ddl().drop(table);
        }

        //定义表 默认当前连接的catalog与schema
        table = new Table<>("FI_ORDER").setComment("财务_订单");
        //添加列
        Column column = new Column("id","bigint").autoIncrement(true).primary(true).setComment("编号");
        table.addColumn(column);
        //也可以这样添加列
        table.addColumn(  "ITEM_QTY",  "int").setComment("");

        table.addColumn("CREATE_TIME", "datetime").setDefaultCurrentDateTime();

        service.ddl().save(table);
        table = service.metadata().table("FI_ORDER");
        table.addColumn("NEW_COLUMN", "varchar(10)");
        service.ddl().save(table);
    }

    /**
     * 主键
     * @throws Exception
     */
    @Test
    public void primary() throws Exception{
        Table table = new Table("FI_ORDER_PK");
        if(service.metadata().exists(table)){
            service.ddl().drop(table);
        }
        table = new Table("SD_ORDER_PK");
        if(service.metadata().exists(table)){
            service.ddl().drop(table);
        }
        table = new Table("PP_ORDER_PK");
        if(service.metadata().exists(table)){
            service.ddl().drop(table);
        }


        table = new Table<>("FI_ORDER_PK").setComment("财务_订单1");
        //可以在列上设置主键标识
        table.addColumn("id","bigint").autoIncrement(true).primary(true).setComment("编号");
        table.addColumn(  "ITEM_QTY",  "int").setComment("");
        service.ddl().create(table);

        //也可以单独添加主键
        table = new Table<>("SD_ORDER_PK").setComment("分销_订单2");
        Column col = table.addColumn("id","bigint").setComment("主键");
        PrimaryKey pk = new PrimaryKey() ;
        pk.addColumn(col);
        table .setPrimaryKey(pk) ;
        service.ddl().create(table);

        //因大部分数据库 要求自增列必须在主键上 所以设置了自增的同时也设置成主键
        table = new Table<>("PP_ORDER_PK").setComment("生产_订单3");
        table.addColumn("id","bigint").autoIncrement(true).setComment("主键");
        service.ddl().create(table);
    }
    /**
     * 查询所有表
     * @throws Exception
     */
    @Test
    public void tables() throws Exception{
        for(int i=0;i <3; i++) {
            LinkedHashMap<String, Table> tables = service.metadata().tables();
            for (String table : tables.keySet()) {
                System.out.println(service.metadata().table(table, true));
                break;
            }
        }
    }

    /**
     * 验证数据源是否可用
     */
    @Test
    public void validate() {
        boolean result = DataSourceHolder.validity("pg");
        log.warn("连接状态:{}",result);
        AnylineService pg = ServiceProxy.service("pg");
        log.warn("连接状态:{}", pg.validity());
        //这样会异常异常，可以捕捉一下用来显示具体异常
        try {
            pg.hit();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    /**
     * 临时数据源
     * @throws Exception
     */
    @Test
    public void temporary() throws Exception{
        for(int i=0; i<10; i++) {
            String url = "jdbc:mysql://localhost:33306/simple?useUnicode=true&characterEncoding=UTF8&useSSL=false&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
            DruidDataSource ds = new DruidDataSource();
            ds.setUrl(url);
            ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
            ds.setUsername("root");
            ds.setPassword("root");
            ds.setConnectionErrorRetryAttempts(3);
            ds.setBreakAfterAcquireFailure(true);
            ds.setConnectTimeout(3000);
            ds.setMaxWait(30000);
            service = ServiceProxy.temporary(ds);
            boolean status = service.validity();
            log.warn("连接状态:{}", status);
            LinkedHashMap<String, Table> tables = service.metadata().tables();
            for (String table : tables.keySet()) {
                System.out.println(service.metadata().table(table, true));
                break;
            }
        }
    }
    /**
     * 复制数据源(与数据源在同一个服务器上的其他数据库)
     * @throws Exception
     */
    @Test
    public void copyDatasource() throws Exception{
        //根据数据源 创建当前数据源所在服务器的其他数据库相关的数据源
        //不提供参数复制默认数据源 default
        //当前服务器上还有simple_crm/simple_sso，以下会再创建两个数据源default_simple_crm/default_simple_sso
        //注意数据名会转成小写、但前缀不会转小写，项目中定义的是什么就是什么
        List<String> list = DataSourceHolder.copy();
        log.warn("创建数据源:{}", list);
        //key已存在，不会重复创建
        list = DataSourceHolder.copy("default");
        log.warn("创建数据源:{}", list);
        log.warn("全部数据源:{}", RuntimeHolder.runtimes.keySet());
        ServiceProxy.service("default_simple_sso").query("sso_user");
        //注销主数据源时  复制出来的子源也一块注销
        RuntimeHolder.destroy("default");
        DataSourceHolder.destroy("default");

        log.warn("全部数据源:{}", RuntimeHolder.runtimes.keySet());

    }
    @Test
    public void copyTable() throws Exception{
        //测试表
        Table table = service.metadata().table("CRM_USER");
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table("CRM_USER");
        Column column = new Column("ID").autoIncrement(true).setType("int").primary(true);
        table.addColumn(column);
        table.addColumn("CODE","varchar(10)").setComment("编号");
        table.addColumn("NAME","varchar(10)").setComment("名称");
        service.ddl().create(table);

        //从第一个库中取出表结构
        table = service.metadata().table("CRM_USER");

        Table chk = ServiceProxy.service("sso").metadata().table("CRM_COPY");
        if(null != chk){
            ServiceProxy.service("sso").ddl().drop(chk);
        }
        table.setName("CRM_COPY");
        //在第二个库中创建
        ServiceProxy.service("sso").ddl().create(table);
    }

}
